<?php

class RecipientTable extends Doctrine_Table {

    public function addActiveRecipientsQuery(Doctrine_Query $q = null) {
        if (is_null($q)) //create a base query if not exists
        {
            $q = Doctrine_Query::create()
                    ->from('Recipient r');
        }
        //otherwise extends the passed one
        $alias = $q->getRootAlias();//to get the root alias, "r" in this case
        $q->andWhere($alias . '.active = ?', true);
        return $q;
    }

    /**
     * Returns the number of user for the filter
     * @param string $filter
     * @return integer
     */
    public function countRecipientsForFilter($recipientFilter = null, $tagsFilter = null) {
        $q = $this->getTargetRecipientsQuery($recipientFilter, $tagsFilter);
        return $q->count();
    }

    /**
     * Returns Recipient's collection for the filter
     * @param string $filter
     * @param integer $pageNum
     * @param integer $pageSize
     * @return Doctrine_Colection
     */
    public function getRecipientsForFilter($recipientFilter, $tagsFilter, $pageNum, $pageSize) {
        // Creating pager object
        $pager = $this->getRecipientsForFilterPager($recipientFilter, $tagsFilter, $pageNum, $pageSize);
        return $pager->execute();
    }

    /**
     * Create a paginator for RecipientsForFilter list
     * @param string $filter
     * @return Doctrine_Pager
     */
    private function getRecipientsForFilterPager($recipientFilter, $tagsFilter, $pageNum=0, $pageSize=25) {
        // Creating pager object
        $pager = new Doctrine_Pager($this->getTargetRecipientsQuery($recipientFilter, $tagsFilter), $pageNum, $pageSize);
        return $pager;
    }

    /**
     * Returns the query
     * @param <type> $filter
     * @return Doctrine_Query
     */
//    private function getRecipientsForFilterQuery($filter){
//        $q = $this->createQuery('r');
//        if(strlen(trim($filter))>0) {
//            $q->addWhere($filter);
//        }
//        return $q;
//    }

    /**
     * This method generates and returns the query for the specified recipientFilter
     * and tagfilter
     * @param string $recipientFilter
     * @param string $tagsFilter
     * @return Doctrine_Query
     */
    public function getTargetRecipientsQuery($recipientFilter = null, $tagsFilter = null) {
        $q = null;
        //Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_PORTABILITY, Doctrine::PORTABILITY_NONE);
        $this->setAttribute(Doctrine_Core::ATTR_PORTABILITY, Doctrine::PORTABILITY_NONE);
//simple query
        if ($recipientFilter != '' && $tagsFilter == '') {
            $q = Doctrine_Query::create()
                            ->from('Recipient r')
                            ->where($recipientFilter);
        }
        //query with HAVING statement
        if ($tagsFilter != '') {
            $tags = Doctrine_Core::getTable('Tag')->getTagsArray();
            $tagquery = '';
            foreach (array_keys($tags) as $tagname) {
                $tagquery .= ', sum(IF(t.keyword=\'' . $tagname . '\',1,0)) AS tag_' . $tagname . ' ';
            }
            $q = Doctrine_Query::create()
//                            ->select("r.*, sum(IF(t.keyword='mostre',1,0)) AS tag_mostre")
                            ->select("r.* " . $tagquery)
                            ->from('Recipient r')
                            ->leftJoin('r.Clicks c')
                            ->leftJoin('c.Link l')
                            ->leftJoin('l.Tags t');
            if ($recipientFilter != '') {// WHERE statement only if needed
                $q->where($recipientFilter);
            }
            $q->groupBy('r.recipient_id')
                    ->having($tagsFilter);
        }
        return $this->addActiveRecipientsQuery($q);
    }

}
